package com.apobates.forum.member.impl.dao;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.EnumMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Optional;
import java.util.TreeMap;
import java.util.stream.Stream;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.cache.annotation.CacheConfig;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.apobates.forum.member.api.dao.MemberDao;
import com.apobates.forum.member.entity.Member;
import com.apobates.forum.member.entity.MemberGroupEnum;
import com.apobates.forum.member.entity.MemberRoleEnum;
import com.apobates.forum.member.entity.MemberStatusEnum;
import com.apobates.forum.utils.Commons;

@Repository
@CacheConfig(cacheNames = "memberCache")
public class MemberDaoImpl implements MemberDao {
	@PersistenceContext
	private EntityManager entityManager;
	private final static Logger logger = LoggerFactory.getLogger(MemberDaoImpl.class);
	
	@Transactional(propagation = Propagation.REQUIRED)
	@Override
	public void save(Member member) {
		entityManager.persist(member);
	}

	@Override
	public Optional<Member> findOne(String memberNames, String encryptPswd) {
		try {
			Member m = entityManager.createQuery("SELECT e FROM Member e WHERE e.status != ?1 AND e.names = ?2 AND e.pswd = ?3", Member.class)
					.setParameter(1, MemberStatusEnum.DELETE)
					.setParameter(2, memberNames)
					.setParameter(3, encryptPswd)
					.getSingleResult();
			if(m.getMgroup().getSymbol() < 1) {
				return Optional.empty();
			}
			return Optional.ofNullable(m);
		} catch (javax.persistence.NoResultException e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}

	@Override
	public Optional<Member> findOneForAdmin(String memberNames, String encryptPswd) {
		try {
			Member m = entityManager.createQuery("SELECT e FROM Member e WHERE e.status != ?1 AND e.names = ?2 AND e.pswd = ?3 AND e.mrole = ?4", Member.class)
					.setParameter(1, MemberStatusEnum.DELETE)
					.setParameter(2, memberNames)
					.setParameter(3, encryptPswd)
					.setParameter(4, MemberRoleEnum.ADMIN)
					.getSingleResult();
			return Optional.ofNullable(m);
		} catch (javax.persistence.NoResultException e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}
	/**
	 * 更新会员的状态,角色,组时要更新缓存@20200502
	 */
	@Cacheable(key="#memberId", unless="#result==null")
	@Override
	public Member findOneById(long memberId){ //[MC]M1
		logger.info("[MemberDAO]method find one arg:"+memberId);
		try {
			Member m = entityManager.find(Member.class, Long.valueOf(memberId));
			return m;
		} catch (Exception e) {
			return null;
		}
	}
	/**
	 * 更新会员的状态,角色,组时要更新缓存@20200502
	 */
	@Transactional(propagation = Propagation.REQUIRED)
	@CacheEvict(key="#memberId")
	@Override
	public boolean editMemberRole(long memberId, MemberRoleEnum role) { //[MC]M1-R1
		int affect = entityManager.createQuery("UPDATE Member m SET m.mrole = ?1 WHERE m.id = ?2").setParameter(1, role).setParameter(2, memberId).executeUpdate();
		return affect == 1;
	}
	
	/**
	 * 更新会员的状态,角色,组时要更新缓存@20200502
	 */
	@Transactional(propagation = Propagation.REQUIRED)
	@CacheEvict(key="#memberId")
	@Override
	public boolean editMemberGroup(long memberId, MemberGroupEnum group) { //[MC]M1-R2
		int affect = entityManager.createQuery("UPDATE Member m SET m.mgroup = ?1 WHERE m.id = ?2").setParameter(1, group).setParameter(2, memberId).executeUpdate();
		return affect == 1;
	}
	
	/**
	 * 更新会员的状态,角色,组时要更新缓存@20200502
	 */
	@Transactional(propagation = Propagation.REQUIRED)
	@CacheEvict(key="#memberId")
	@Override
	public boolean editMemberStatus(long memberId, MemberStatusEnum status) { //[MC]M1-R3
		int affect = entityManager.createQuery("UPDATE Member m SET m.status = ?1 WHERE m.id = ?2").setParameter(1, status).setParameter(2, memberId).executeUpdate();
		return affect == 1;
	}
	/**
	 * 更新会员的昵称和个性签名时要更新缓存@20200502
	 */
	@Transactional(propagation = Propagation.REQUIRED)
	@CacheEvict(key="#id")
	@Override
	public Optional<Boolean> editNicknameAndSignature(long id, String nickname, String signature) {
		try {
			int affect = entityManager.createQuery("UPDATE Member m SET m.signature = ?1, m.nickname = ?2 WHERE m.id = ?3").setParameter(1, signature).setParameter(2, nickname).setParameter(3, id).executeUpdate();
			return affect == 1?Optional.of(true):Optional.empty();
		}catch(Exception e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}
	
	@Override
	public Optional<String> findSalt(String memberNames) {// salt
		try {
			String salt = entityManager.createQuery("SELECT e.salt FROM Member e WHERE e.status != ?1 AND e.names = ?2", String.class)
					.setParameter(1, MemberStatusEnum.DELETE)
					.setParameter(2, memberNames)
					.getSingleResult();
			return Optional.ofNullable(salt);
		} catch (javax.persistence.NoResultException e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}

	@Transactional(propagation = Propagation.REQUIRED)
	@Override
	public int editPswd(long memberId, String oldEncryptPswd, String newEncryptPswd, String newPswdSalt) {
		return entityManager.createQuery("UPDATE Member m SET m.pswd = ?1, m.salt = ?2  WHERE m.id = ?3 AND m.status != ?4 AND m.pswd = ?5")
				.setParameter(1, newEncryptPswd)
				.setParameter(2, newPswdSalt)
				.setParameter(3, memberId)
				.setParameter(4, MemberStatusEnum.DELETE)
				.setParameter(5, oldEncryptPswd)
				.executeUpdate();
	}

	@Override
	public Optional<String> findSalt(long id) {
		try {
			String salt = entityManager.createQuery("SELECT e.salt FROM Member e WHERE e.status != ?1 AND e.id = ?2", String.class)
					.setParameter(1, MemberStatusEnum.DELETE)
					.setParameter(2, id)
					.getSingleResult();
			return Optional.ofNullable(salt);
		} catch (javax.persistence.NoResultException e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}
	@Transactional(propagation = Propagation.REQUIRED)
	@Override
	public int resetPswd(long id, String newEncryptPswd, String newPswdSalt) {
		return entityManager.createQuery("UPDATE Member m SET m.pswd = ?1, m.salt = ?2  WHERE m.id = ?3 AND m.status != ?4")
				.setParameter(1, newEncryptPswd)
				.setParameter(2, newPswdSalt)
				.setParameter(3, id)
				.setParameter(4, MemberStatusEnum.DELETE)
				.executeUpdate();
	}

	@Override
	public long exists(String memberNames) {
		if(!Commons.isNotBlank(memberNames)){
			return -1L;
		}
		try {
			Long memberId = entityManager.createQuery("SELECT e.id FROM Member e WHERE e.names = ?1", Long.class)
					.setParameter(1, memberNames)
					.getSingleResult();
			return memberId;
		} catch (javax.persistence.NoResultException e) {
			return 0L;
		}
	}
	


	@Override
	public Optional<String> getAvatar(long id) {
		try {
			String avatar =  entityManager.createQuery("SELECT m.avatarURI FROM Member m WHERE m.id = ?1", String.class).setParameter(1, id).getSingleResult();
			return Optional.ofNullable(avatar);
		}catch(Exception e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}
	
	@Transactional(propagation = Propagation.REQUIRED)
	@Override
	public Optional<Boolean> editAvatar(long id, String encodeAvatarPath) {
		try {
			int affect = entityManager.createQuery("UPDATE Member m SET m.avatarURI = ?1 WHERE m.id = ?2").setParameter(1, encodeAvatarPath).setParameter(2, id).executeUpdate();
			return affect == 1?Optional.of(true):Optional.empty();
		}catch(Exception e) {
			if(logger.isDebugEnabled()){
				logger.debug(e.getMessage(), e);
			}
		}
		return Optional.empty();
	}

	@Override
	public Stream<Member> findAllByIdList(List<Long> idList) {
		if(idList==null || idList.isEmpty()){
			return Stream.empty();
		}
		return entityManager.createQuery("SELECT m FROM Member m WHERE m.id IN ?1", Member.class).setParameter(1, idList).getResultStream();
	}

	@Override
	public Stream<Member> findAllOfRecent(int size) {
		return entityManager.createQuery("SELECT m FROM Member m ORDER BY m.registeDateTime DESC", Member.class).setMaxResults(size).getResultStream();
	}

	@Override
	public long count() {
		try {
			return entityManager.createQuery("SELECT COUNT(m) FROM Member m", Long.class).getSingleResult();
		} catch (Exception e) {
			if (logger.isDebugEnabled()) {
				logger.debug("[count][MemberDao]", e);
			}
		}
		return 0L;
	}

	@Override
	public Stream<Member> searchByNames(String namesWordChar, int showSize) {
		return entityManager.createQuery("SELECT m FROM Member m WHERE LOCATE(:name, m.names)>0 ORDER BY m.id DESC", Member.class)
				.setParameter("name", namesWordChar)
				.setMaxResults(showSize)
				.getResultStream();
	}
	@Override
	public Stream<Member> searchByNames(String namesWordChar) {
		return entityManager
				.createQuery("SELECT m FROM Member m WHERE LOCATE(:name, m.names)>0 ORDER BY m.id DESC", Member.class)
				.setParameter("name", namesWordChar)
				.getResultStream();
	}

	@Transactional(propagation = Propagation.REQUIRED)
	@Override
	public int editMemberStatus(Map<Long,MemberStatusEnum> status) {
		Integer affect = 0;
		for (Entry<Long,MemberStatusEnum> entry : status.entrySet()) {
			if(editMemberStatus(entry.getKey(), entry.getValue())){
				affect += 1;
			}
		}
		return affect;
	}

	@Override
	public TreeMap<String, Long> groupMemberForBirthDate(LocalDateTime start, LocalDateTime finish) {
		final String SQL="SELECT FUNCTION('DATE', m.registeDateTime), COUNT(m) FROM Member m WHERE m.registeDateTime BETWEEN ?1 AND ?2 GROUP BY FUNCTION('DATE', m.registeDateTime)";
		@SuppressWarnings("unchecked")
		List<Object[]> result = entityManager.createQuery(SQL).setParameter(1, start).setParameter(2, finish).getResultList();
		//
		TreeMap<String, Long> data = new TreeMap<>();
		for(Object[] columns : result){
			String k = null;
			try{
				k = (String)columns[0];
			}catch(java.lang.ClassCastException e){
				k = columns[0].toString();
			}
			Long v = 0L;
			try{
				v = ((BigDecimal)columns[1]).longValue();
			}catch(java.lang.ClassCastException e){
				v = (Long)columns[1];
			}
			if(k==null || v == null){
				continue;
			}
			data.put(k, v);
		}
		return data;
	}

	@Override
	public EnumMap<MemberStatusEnum, Long> groupMemberForStatus() {
		final String SQL="SELECT m.status, COUNT(m) FROM Member m GROUP BY m.status";
		@SuppressWarnings("unchecked")
		List<Object[]> result = entityManager.createQuery(SQL).getResultList();
		//
		EnumMap<MemberStatusEnum, Long> data = new EnumMap<>(MemberStatusEnum.class);
		for(Object[] columns : result){
			MemberStatusEnum k = null;
			try{
				k = (MemberStatusEnum)columns[0];
			}catch(java.lang.ClassCastException e){}
			Long v = 0L;
			try{
				v = ((BigDecimal)columns[1]).longValue();
			}catch(java.lang.ClassCastException e){
				v = (Long)columns[1];
			}
			if(k==null || v == null){
				continue;
			}
			data.put(k, v);
			
		}
		return data;
	}

	@Override
	public EnumMap<MemberRoleEnum, Long> groupMemberForRole() {
		final String SQL="SELECT m.mrole, COUNT(m) FROM Member m GROUP BY m.mrole";
		@SuppressWarnings("unchecked")
		List<Object[]> result = entityManager.createQuery(SQL).getResultList();
		//
		EnumMap<MemberRoleEnum, Long> data = new EnumMap<>(MemberRoleEnum.class);
		for(Object[] columns : result){
			MemberRoleEnum k = null;
			try{
				k = (MemberRoleEnum)columns[0];
			}catch(java.lang.ClassCastException e){}
			Long v = 0L;
			try{
				v = ((BigDecimal)columns[1]).longValue();
			}catch(java.lang.ClassCastException e){
				v = (Long)columns[1];
			}
			if(k==null || v == null){
				continue;
			}
			data.put(k, v);
			
		}
		return data;
	}

	@Override
	public EnumMap<MemberGroupEnum, Long> groupMemberForGroup() {
		final String SQL="SELECT m.mgroup, COUNT(m) FROM Member m GROUP BY m.mgroup";
		@SuppressWarnings("unchecked")
		List<Object[]> result = entityManager.createQuery(SQL).getResultList();
		//
		EnumMap<MemberGroupEnum, Long> data = new EnumMap<>(MemberGroupEnum.class);
		for(Object[] columns : result){
			MemberGroupEnum k = null;
			try{
				k = (MemberGroupEnum)columns[0];
			}catch(java.lang.ClassCastException e){}
			Long v = 0L;
			try{
				v = ((BigDecimal)columns[1]).longValue();
			}catch(java.lang.ClassCastException e){
				v = (Long)columns[1];
			}
			if(k==null || v == null){
				continue;
			}
			data.put(k, v);
			
		}
		return data;
	}
}